package com.supermap.dataservice.cloud.survey;

import com.supermap.dataservice.util.Table;
import com.supermap.dubbo.api.cloud.survey.Surveyanswer;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年12月11 下午 3:36
 * @email 1301584033@qq.com
 */
@Mapper
public interface SurveyMapper {

    /***
     *
     * **/
    /***
     * 删除问卷
     * */
    @Delete("DELETE FROM t_surveycategory where id=#{id}")
    int delSurvey(@Param("id") String id);

    /**
     * 删除问卷使用区域
     **/
    @Delete("DELETE FROM t_surveyarea where surveyid=#{surveyid}")
    int delSurveyArea(@Param("surveyid") String surveyid);

    /***
     * 删除大题
     * */
    @DeleteProvider(type = SurveyMapperSQL.class, method = "delSurveyMenu")
    int delSurveyMenu(@Param("surveyid") String surveyid, @Param("id") String id);

    /**
     * 删除小题
     **/
    @DeleteProvider(type = SurveyMapperSQL.class, method = "delSurveyContent")
    int delSurveyContent(@Param("menuid") String menuid, @Param("id") String id);

    /**
     * 删除选项
     **/
    @Delete("delete from t_surveyoptions where contentid=#{contentid}")
    int delSurveyOptions(@Param("contentid") String contentid);

    /**
     * 删除答案
     */
    @Delete("DELETE from t_surveyanswer where contentid=#{contentid}")
    int delSurveyAnswer(@Param("contentid") String contentid);

    /***
     * 新增和更新问卷调查问题
     * **/
    /**
     * 获取问卷大题
     **/
    @Select("select id,name,surveyid,orderid,createTime from t_surveymenu where surveyid=#{surveyId}")
    List<SurveyMenu> getSurveyMenuName(@Param("surveyId") String surveyId);

    /**
     * 获取sms登陆用户权限
     **/
    @Select("select tt.rolecode from t_s_role_user t LEFT JOIN t_s_role tt on t.roleid=tt.id where t.userid=#{userid}")
    String getSmsUserRole(@Param("userid") String userid);

    /**
     * 获取该区域下所有的用户
     */
    @SelectProvider(type = SurveyMapperSQL.class, method = "getSmsUserListId")
    List<String> getSmsUserListId(@Param("areaCode") String areaCode);

    /**
     * 根据sms用户获取试卷名称
     **/
    @SelectProvider(type = SurveyMapperSQL.class, method = "getSurveyName")
    List<SurveyDoc> getSurveyName(@Param("userId") String userId);
    /**
     * 问卷名获取
     * **/
    @Select("select id from t_surveycategory where `name`=#{name}")
    com.supermap.dubbo.api.cloud.survey.SurveyDoc getSurveyDocNameNum(@Param("name")String name);

    /**
     * 问卷名获取
     * **/
    @Select("select id from t_surveycontent where menuid=#{menuid}  ORDER BY id DESC LIMIT 1")
    com.supermap.dubbo.api.cloud.survey.SurveyContent getContentId(@Param("menuid")Integer menuid);

    /***
     * 更新试卷信息
     * */
    @UpdateProvider(type = SurveyMapperSQL.class, method = "updateSurveycategory")
    int updateSurveycategory(@Param("surveyDoc")com.supermap.dubbo.api.cloud.survey.SurveyDoc surveyDoc);

    /**
     * 更新适用区域
     **/
    @Update("update t_surveyarea set surveyid=#{surveyid},#{areaCode}")
    int updateSurveyAreaCode(@Param("surveyid") String surveyid, @Param("areaCode") String areaCode);

    /**
     * 更新大题
     **/
    @UpdateProvider(type = SurveyMapperSQL.class, method = "updateSurveyMenu")
    int updateSurveyMenu(@Param("surveyMenu")com.supermap.dubbo.api.cloud.survey.SurveyMenu surveyMenu);

    /**
     * 更新小题
     */
    @UpdateProvider(type = SurveyMapperSQL.class, method = "updateSurveyContent")
    int updateSurveyContent(@Param("content") com.supermap.dubbo.api.cloud.survey.SurveyContent content);

    /**
     * 更新选项
     **/
    @UpdateProvider(type = SurveyMapperSQL.class, method = "updateSurveyOption")
    int updateSurveyOption(@Param("surveyOptions") com.supermap.dubbo.api.cloud.survey.SurveyOptions surveyOptions);

    /**
     * 插入试卷
     */
    @InsertProvider(type = SurveyMapperSQL.class, method = "insertSurveycategory")
    int insertSurveycategory(@Param("surveyDoc") com.supermap.dubbo.api.cloud.survey.SurveyDoc surveyDoc);

    /***
     * 插入使用区域
     * */
    @Insert("insert into t_surveyarea (surveyid,areaCode) VALUES(#{surveyid},#{areaCode})")
    int insertSurveyAreaCode(@Param("surveyid") int surveyid, @Param("areaCode") String areaCode);

    /**
     * 插入大题标题
     **/
    @Insert("insert into t_surveymenu (surveyid,name,orderid) VALUES(#{surveyMenu.surveyid},#{surveyMenu.name},#{surveyMenu.orderid})")
//    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", resultType = Long.class, before = false)
    int insertSurveyMenu(@Param("surveyMenu") com.supermap.dubbo.api.cloud.survey.SurveyMenu surveyMenu);

    /**
     * 插入小标题
     */
    @Insert("insert into t_surveycontent (menuid,topic,content,orderid) VALUES(#{surveyContent.menuid},#{surveyContent.topic},#{surveyContent.content},#{surveyContent.orderid})")
    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", resultType = Long.class, before = false)
    int insertSurveyContent(@Param("surveyContent") com.supermap.dubbo.api.cloud.survey.SurveyContent surveyContent);

    /**
     * 插入选项
     */
    @Insert("insert into t_surveyoptions (contentid,orderid,`option`) VALUES(#{surveyOptions.contentid},#{surveyOptions.orderid},#{surveyOptions.option})")
    int insertSurveyOption(@Param("surveyOptions") com.supermap.dubbo.api.cloud.survey.SurveyOptions surveyOptions);

    @Select("select username userName from t_user where id=#{userId}")
    SurveyDoc getUserNameByUserId(@Param("userId") String userId);

    /**
     * 获取问卷回答人对应的答案
     */
    @Select("SELECT\n" +
            "\tt.id id,\n" +
            "\tt.topic topic,\n" +
            "\tt.content content,t.orderid,\n" +
            "\tGROUP_CONCAT(t2.orderid) as ansId\n" +
            "FROM\n" +
            "\tt_surveycontent t\n" +
            "\tLEFT JOIN t_surveyanswer t1 ON t.id = t1.contentid \n" +
            "\tLEFT JOIN t_surveyoptions t2 on t1.`option`=t2.id\n" +
            "WHERE\n" +
            "\tt.menuid = #{id} and t1.userid=#{userId} \n" +
            " GROUP BY t.id ORDER BY\n" +
            "\tt.orderid ASC")
    List<SurveyContent> getUserOptionByIdAndUserId(@Param("id") Integer id, @Param("userId") String userId);

    /**
     * 根据问卷id获取回答人员名称
     **/
    @SelectProvider(type = SurveyMapperSQL.class, method = "getAnsSurveyUserNameBySurveyId")
    List<SurveyDoc> getAnsSurveyUserNameBySurveyId(@Param("table") Table table, @Param("surveyId") String surveyId, @Param("areaCode") String areaCode);

    @SelectProvider(type = SurveyMapperSQL.class, method = "getCountSurveyUserNameBySurveyId")
    int getCountSurveyUserNameBySurveyId(@Param("surveyId") String surveyId, @Param("areaCode") String areaCode);

    /***
     * 获取每题选项比例
     * */
    @SelectProvider(type = SurveyMapperSQL.class, method = "getSurveryPre")
    List<SurveyDoc> getSurveryPre(@Param("contentid") String contentid, @Param("areaCode") String areaCode);

    /**
     * sms统计按时间筛选计算比例
     **/
    @SelectProvider(type = SurveyMapperSQL.class, method = "getServeySmsBySendTime")
    List<SurveyDoc> getServeySmsBySendTime(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("table") Table table, @Param("areaCode") String areaCode);

    @SelectProvider(type = SurveyMapperSQL.class, method = "getCountServeySmsBySendTime")
    int getCountServeySmsBySendTime(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("table") Table table, @Param("areaCode") String areaCode);

    /**
     * sms问卷列表
     **/
    @SelectProvider(type = SurveyMapperSQL.class, method = "getSurveyByAreaCode")
    List<SurveyDoc> getSurveyByAreaCodeSMS(@Param("areaCode") int areaCode, @Param("table") Table table);

    @SelectProvider(type = SurveyMapperSQL.class, method = "getSurveyByAreaCodeCountSMS")
    int getSurveyByAreaCodeCountSMS(@Param("areaCode") int areaCode);

    /**
     * 获取问卷调查的适用区域
     */
    @Select("select userName from t_s_base_user where id=#{id}")
    String getSurveyByIdSms(@Param("id") String id);

    /**
     * 通过区域查询所有问卷调查
     */
    @SelectProvider(type = SurveyMapperSQL.class, method = "getSurveyCategoryByAreaCodeSQL")
    List<Map<String, Object>> getSurveyCategoryByAreaCode(@Param("areaCode") String areaCode,@Param("userId")Integer userId);

    /**
     * 通过问卷ID查询问卷
     */
//    @Select("select id,name,title,`describe` from t_surveycategory where id=#{id}")
    @Select("SELECT\n" +
            "\ta.id,a.name,a.title,a.`describe`,a.userid,\n" +
            "\tGROUP_CONCAT( b.areacode ) areaCode,\n" +
            "\tGROUP_CONCAT(c.areaname) areaName\n" +
            "FROM\n" +
            "\tt_surveycategory a\n" +
            "\tLEFT JOIN t_surveyarea b ON a.id = b.surveyid\n" +
            "LEFT JOIN t_area c on b.areacode=c.areacode\n" +
            "where a.id=#{id}\n" +
            "GROUP BY\n" +
            "\ta.id")
    SurveyDoc getSurveyByCategoryId(@Param("id") Integer id);

    /**
     * 查大类
     */
    @Select("select id,name from t_surveymenu where surveyid=#{id} order by orderid asc")
    List<SurveyMenu> getSurveyMenuByContent(@Param("id") Integer id);

    /**
     * 获取一条大题
     * **/
    @Select("select id,name from t_surveymenu where id=#{id} ")
    List<SurveyMenu> getSurveyMenu(@Param("id") Integer id);

    /**
     * 查内容
     */
    @Select("select id,topic,orderid,content,menuid from t_surveycontent where menuid=#{id} order by orderid asc")
    List<SurveyContent> getSurveyContent(@Param("id") Integer id);
    /***
     * 查询大于
     * **/
    @Select("select id,topic,orderid,content,menuid from t_surveycontent where id>#{id} and menuid=#{menuid} order by orderid asc")
    List<SurveyContent> getSurveyContentBigId(@Param("id") String id,@Param("menuid")String menuid);

    /**
     * 查询一条小题
     **/
    @Select("select id,topic,orderid,content,menuid from t_surveycontent where id=#{id}")
    List<SurveyContent> getContent(@Param("id") Integer id);

    /**
     * 查选项
     */
    @Select("select id,orderid,`option`,`contentid` from t_surveyoptions where contentid=#{id} order by orderid asc")
    List<SurveyOptions> getSurveyOptions(@Param("id") Integer id);

    /**
     * 查询答案
     **/
    @Select("select * from t_surveyanswer where contentid=#{contentid}")
    List<Surveyanswer> getSurveyAnswers(@Param("contentid") Integer contentid);

    /**
     * 上传问题答案
     */
    @InsertProvider(type = SurveyMapperSQL.class, method = "putSurveySQL")
    Integer putSurvey(@Param("surveyanswerList") List<Surveyanswer> surveyanswerList);


    class SurveyMapperSQL {

        public String getSurveyCategoryByAreaCodeSQL(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            Integer userId = (Integer) map.get("userId");
            StringBuilder stringBuilder = new StringBuilder();

            if(userId==null&&areaCode!=null){
                stringBuilder.append(" select id,title from t_surveycategory where  areacode like concat('"+areaCode+"','%') ");
            }else{
                stringBuilder.append(" select * from t_notice t1 where t1.noticetype=8  and t1.receiveuserid="+userId+" ");
            }

            return stringBuilder.toString();
        }
        public String putSurveySQL(Map<String, Object> map) {
            List<Surveyanswer> surveyanswerList = (List<Surveyanswer>) map.get("surveyanswerList");
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append(" insert into t_surveyanswer(contentid,`option`,userid) ");
            stringBuilder.append(" values ");
            if (surveyanswerList != null && surveyanswerList.size() > 0) {
                for (Surveyanswer surveyanswer : surveyanswerList) {
                    stringBuilder.append(" (" + surveyanswer.getContentid() + "" +
                            "," + surveyanswer.getOption() + "," +
                            "" + surveyanswer.getUserid() + "),");
                }
            }
            return stringBuilder.substring(0, stringBuilder.lastIndexOf(",")).toString();
        }

        /**
         * sms查询问卷列表
         */
        public String getSurveyByAreaCode(Map<String, Object> map) {
            int areaCode = (int) map.get("areaCode");
            Table table = (Table) map.get("table");
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT a.id,a.NAME,d.areacode,a.userid,a.issend,a.title,a.`describe`,a.sendtime,a.userid,a.createtime,d.areaname FROM t_surveycategory a LEFT JOIN (\n" +
                    "SELECT c.surveyid surveyid,c.areacode areacode,GROUP_CONCAT(DISTINCT b.areaname) areaname FROM t_surveyarea c LEFT JOIN t_area b ON c.areacode=b.areacode GROUP BY c.surveyid) d ON a.id=d.surveyid WHERE 1=1");
            if (areaCode > 0) {
                sql.append(" and d.areacode LIKE '" + areaCode + "%'");
            }
            sql.append(" ORDER BY a.createtime LIMIT " + table.getStartPoint() + "," + table.getLimit() + ";");
            return sql.toString();
        }

        public String getSurveyByAreaCodeCountSMS(Map<String, Object> map) {
            int areaCode = (int) map.get("areaCode");
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT count(a.id) FROM t_surveycategory a LEFT JOIN (\n" +
                    "SELECT c.surveyid surveyid,c.areacode areacode,GROUP_CONCAT(DISTINCT b.areaname) areaname FROM t_surveyarea c" +
                    " LEFT JOIN t_area b ON c.areacode=b.areacode GROUP BY c.surveyid) d ON a.id=d.surveyid WHERE 1=1 ");
            if (areaCode>0) {
                sql.append(" and d.areaCode like '" + areaCode + "%'");
            }
            return sql.toString();
        }

        public String getServeySmsBySendTime(Map<String, Object> map) {
            String startTime = (String) map.get("startTime");
            String endTime = (String) map.get("endTime");
            String areaCode = (String) map.get("areaCode");
            Table table = (Table) map.get("table");
            StringBuffer sql = new StringBuffer();
            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();

            sql.append("SELECT\n" +
                    "\ts1.yread,\n" +
                    "\ts1.nread,\n" +
                    "\ts1.countNum,\n" +
                    "\t( s1.yread / s1.countNum * 100 ) yrate,\n" +
                    "\t( s1.nread / s1.countNum * 100 ) nrate,\n" +
                    "\ts1.messageid id,\n" +
                    "\tts.name,ts.sendtime\n" +
                    "FROM\n" +
                    "\t(\n" +
                    "\tSELECT\n" +
                    "\t\tMAX( CASE s.isread WHEN 1 THEN s.num ELSE 0 END ) yread,\n" +
                    "\t\tMAX( CASE s.isread WHEN 0 THEN s.num ELSE 0 END ) nread,\n" +
                    "\t\tSUM( s.num ) countNum,\n" +
                    "\t\ts.messageid messageid \n" +
                    "\tFROM\n" +
                    "\t\t(\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tt.messageid messageid,\n" +
                    "\t\t\tt.isread isread,\n" +
                    "\t\t\tcount( t.id ) num\n" +
                    "\t\tFROM\n" +
                    "\t\t\tt_notice t INNER JOIN t_user tt on t.receiveuserid=tt.id\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tt.noticetype = 8 \n" +
                    "\t\t\tAND t.del_flg = 0 ");

            if (areaCode != null && areaCode != "null" && !"".equals(areaCode)) {
                sql1.append(" and tt.areaCode like '" + areaCode + "%'");
            }
            sql2.append("GROUP BY\n" +
                    "\t\t\tt.messageid,\n" +
                    "\t\t\tt.isread \n" +
                    "\t\t) s \n" +
                    "\t) s1 INNER JOIN t_surveycategory ts on s1.messageid=ts.id\n" +
                    "\twhere 1=1 ");
            if (startTime != null && startTime != "null" && !"".equals(startTime)) {
                sql2.append(" and ts.sendtime >='" + startTime + "'");
            }
            if (endTime != null && endTime != "null" && !"".equals(endTime)) {
                sql2.append(" and ts.sendtime <='" + endTime + "'");
            }
            sql2.append(" ORDER BY ts.sendtime LIMIT " + table.getStartPoint() + "," + table.getLimit() + ";");
            return sql.toString() + sql1.toString() + sql2.toString();
        }

        public String getCountServeySmsBySendTime(Map<String, Object> map) {
            String startTime = (String) map.get("startTime");
            String endTime = (String) map.get("endTime");
            String areaCode = (String) map.get("areaCode");
            Table table = (Table) map.get("table");
            StringBuffer sql = new StringBuffer();
            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();

            sql.append("SELECT\n" +
                    "\tcount(1)\n" +
                    "FROM\n" +
                    "\t(\n" +
                    "\tSELECT\n" +
                    "\t\tMAX( CASE s.isread WHEN 1 THEN s.num ELSE 0 END ) yread,\n" +
                    "\t\tMAX( CASE s.isread WHEN 0 THEN s.num ELSE 0 END ) nread,\n" +
                    "\t\tSUM( s.num ) countNum,\n" +
                    "\t\ts.messageid messageid \n" +
                    "\tFROM\n" +
                    "\t\t(\n" +
                    "\t\tSELECT\n" +
                    "\t\t\tt.messageid messageid,\n" +
                    "\t\t\tt.isread isread,\n" +
                    "\t\t\tcount( t.id ) num\n" +
                    "\t\tFROM\n" +
                    "\t\t\tt_notice t INNER JOIN t_user tt on t.receiveuserid=tt.id\n" +
                    "\t\tWHERE\n" +
                    "\t\t\tt.noticetype = 8 \n" +
                    "\t\t\tAND t.del_flg = 0 ");
            if (areaCode != null && areaCode != "null" && !"".equals(areaCode)) {
                sql1.append(" and tt.areaCode like '" + areaCode + "%'");
            }
            sql2.append("GROUP BY\n" +
                    "\t\t\tt.messageid,\n" +
                    "\t\t\tt.isread \n" +
                    "\t\t) s \n" +
                    "\t) s1 INNER JOIN t_surveycategory ts on s1.messageid=ts.id\n" +
                    "\twhere 1=1 ");
            if (startTime != null && startTime != "null" && !"".equals(startTime)) {
                sql2.append(" and ts.sendtime >='" + startTime + "'");
            }
            if (endTime != null && endTime != "null" && !"".equals(endTime)) {
                sql2.append(" and ts.sendtime <='" + endTime + "'");
            }
            return sql.toString() + sql1.toString() + sql2.toString();
        }

        public String getAnsSurveyUserNameBySurveyId(Map<String, Object> map) {
            String surveyId = (String) map.get("surveyId");
            String areaCode = (String) map.get("areaCode");
            Table table = (Table) map.get("table");
            StringBuffer sql = new StringBuffer();
            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();

            sql.append("SELECT\n" +
                    "\tt.messageid id,\n" +
                    "\tt.receiveuserid userId,\n" +
                    "\tt1.username userName ,\n" +
                    "\tt2.areaname areaName,\n" +
                    "\tt4.name roleName,\n" +
                    "\tt.uploadTime\n" +
                    "FROM\n" +
                    "\tt_notice t\n" +
                    "\tLEFT JOIN t_user t1 ON t.receiveuserid = t1.id ");
            if (areaCode != null && !"".equals(areaCode)) {
                sql1.append(" AND t1.areaCode LIKE '" + areaCode + "%'");
            }
            sql2.append(" LEFT JOIN t_area t2 ON t1.AreaCode = t2.areacode\n" +
                    "LEFT join t_user_role t3 on t1.id=t3.userid\t\n" +
                    "INNER JOIN t_role t4 on t3.roleid=t4.id\n" +
                    "WHERE\n" +
                    "\tt.noticetype = 8 \n" +
                    "\tAND t.isread = 1 \n" +
                    "\tAND t.del_flg = 0  ");
            if (surveyId != null && !"".equals(surveyId)) {
                sql2.append(" AND t.messageid = " + surveyId);
            }
            sql2.append(" LIMIT " + table.getStartPoint() + "," + table.getLimit() + ";");
            return sql.toString() + sql1.toString() + sql2.toString();
        }

        public String getCountSurveyUserNameBySurveyId(Map<String, Object> map) {
            String surveyId = (String) map.get("surveyId");
            String areaCode = (String) map.get("areaCode");
            StringBuffer sql = new StringBuffer();
            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();

            sql.append("SELECT\n" +
                    "count(1)" +
                    "FROM\n" +
                    "\tt_notice t\n" +
                    "\tLEFT JOIN t_user t1 ON t.receiveuserid = t1.id ");
            if (areaCode != null && !"".equals(areaCode)) {
                sql1.append(" AND t1.areaCode LIKE '" + areaCode + "%'");
            }
            sql2.append(" LEFT JOIN t_area t2 ON t1.AreaCode = t2.areacode\n" +
                    "LEFT join t_user_role t3 on t1.id=t3.userid\t\n" +
                    "INNER JOIN t_role t4 on t3.roleid=t4.id\n" +
                    "WHERE\n" +
                    "\tt.noticetype = 8 \n" +
                    "\tAND t.isread = 1 \n" +
                    "\tAND t.del_flg = 0  ");
            if (surveyId != null && !"".equals(surveyId)) {
                sql2.append(" AND t.messageid = " + surveyId);
            }
            return sql.toString() + sql1.toString() + sql2.toString();
        }

        public String getSurveryPre(Map<String, Object> map) {
            String contentid = (String) map.get("contentid");
            String areaCode = (String) map.get("areaCode");
            StringBuffer sql = new StringBuffer();
            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();
            sql.append("SELECT\n" +
                    "\tt.contentid,\n" +
                    "\tt.orderid,\n" +
                    "\tcount( t1.userid ) answerNum,\n" +
                    "\tROUND(\n" +
                    "\t\tcount( userid ) / ( SELECT count(DISTINCT tt.userid ) FROM t_surveyanswer tt WHERE tt.contentid = '2' ) * 100,2) pre \n" +
                    "FROM\n" +
                    "\t`t_surveyoptions` t\n" +
                    "\tLEFT JOIN t_surveyanswer t1 ON t.id = t1.OPTION\n" +
                    "\tLEFT JOIN t_user t2 on t1.userid = t2.id ");
            if (areaCode != null && !"".equals(areaCode)) {
                sql1.append(" and t2.areacode like '" + areaCode + "%'");
            }
            sql2.append(" WHERE 1=1 ");
            if (contentid != null && !"".equals(contentid)) {
                sql2.append(" and t.contentid = " + contentid);
            }
            sql2.append(" GROUP BY t.orderid");
            return sql.toString() + sql1.toString() + sql2.toString();
        }

        public String insertSurveycategory(Map<String, Object> map) {
            com.supermap.dubbo.api.cloud.survey.SurveyDoc surveyDoc = (com.supermap.dubbo.api.cloud.survey.SurveyDoc) map.get("surveyDoc");
            StringBuffer sql = new StringBuffer();
            sql.append("insert into t_surveycategory (`name`,issend,title,`describe`,userid,sendtime) VALUES(");
            if (surveyDoc.getName() != null && !"".equals(surveyDoc.getName())) {
                sql.append("'" + surveyDoc.getName() + "',");
            }
            if (surveyDoc.getIssend() !=null&&!"".equals(surveyDoc.getIssend())) {
                sql.append("'" + surveyDoc.getIssend() + "',");
            }
            if (surveyDoc.getTitle() != null && !"".equals(surveyDoc.getTitle())) {
                sql.append("'" + surveyDoc.getTitle() + "',");
            }
            if (surveyDoc.getDescribe() != null && !"".equals(surveyDoc.getDescribe())) {
                sql.append("'" + surveyDoc.getDescribe() + "',");
            }
            if (surveyDoc.getUserId() != null && !"".equals(surveyDoc.getUserId())) {
                sql.append("'" + surveyDoc.getUserId() + "',");
            }
            if (surveyDoc.getSendTime() != null && !"".equals(surveyDoc.getSendTime())) {
                sql.append("'" + surveyDoc.getSendTime() + "')");
            }
            return sql.toString();
        }

        public String updateSurveycategory(Map<String, Object> map) {

            com.supermap.dubbo.api.cloud.survey.SurveyDoc surveyDoc = (com.supermap.dubbo.api.cloud.survey.SurveyDoc) map.get("surveyDoc");
            StringBuffer sql = new StringBuffer();
            sql.append("UPDATE t_surveycategory t set ");
            if (surveyDoc.getId() != null && !"".equals(surveyDoc.getId())) {
                sql.append(" t.id='" + surveyDoc.getId() + "'");
            }
            if (surveyDoc.getName() != null && !"".equals(surveyDoc.getName())) {
                sql.append(" ,t.name='" + surveyDoc.getName() + "'");
            }
            if (surveyDoc.getIssend() !=null&&!"".equals(surveyDoc.getIssend())) {
                sql.append(" ,t.issend='" + surveyDoc.getIssend() + "'");
            }
            if (surveyDoc.getTitle() != null && !"".equals(surveyDoc.getTitle())) {
                sql.append(" ,t.title='" + surveyDoc.getTitle() + "'");
            }
            if (surveyDoc.getDescribe() != null && !"".equals(surveyDoc.getDescribe())) {
                sql.append(" ,t.`describe`='" + surveyDoc.getDescribe() + "'");
            }
            sql.append(" where 1=1");
            if (surveyDoc.getId() != null && !"".equals(surveyDoc.getId())) {
                sql.append(" and t.id='" + surveyDoc.getId() + "'");
            }
            return sql.toString();
        }

        public String updateSurveyMenu(Map<String, Object> map) {
            com.supermap.dubbo.api.cloud.survey.SurveyMenu surveyMenu = (com.supermap.dubbo.api.cloud.survey.SurveyMenu) map.get("surveyMenu");
            StringBuffer sql = new StringBuffer();
            sql.append("UPDATE t_surveymenu set ");
            if (surveyMenu.getId() != null && !"".equals(surveyMenu.getId())) {
                sql.append(" id='" + surveyMenu.getId() + "'");
            }
            if (surveyMenu.getName() != null && !"".equals(surveyMenu.getName())) {
                sql.append(" ,name='" + surveyMenu.getName() + "' ");
            }
            if (surveyMenu.getOrderid() != null && !"".equals(surveyMenu.getOrderid())) {
                sql.append(" ,orderid='" + surveyMenu.getOrderid() + "'");
            }
            sql.append(" where 1=1 ");
            if (surveyMenu.getId() != null && !"".equals(surveyMenu.getId())) {
                sql.append(" and id='" + surveyMenu.getId() + "'");
            }
            return sql.toString();
        }

        public String updateSurveyContent(Map<String, Object> map) {
            com.supermap.dubbo.api.cloud.survey.SurveyContent content = (com.supermap.dubbo.api.cloud.survey.SurveyContent) map.get("content");
            StringBuffer sql = new StringBuffer();
            sql.append("UPDATE t_surveycontent set ");
            if (content.getId() != null && !"".equals(content.getId())) {
                sql.append(" id='" + content.getId() + "'");
            }
            if (content.getContent() != null && !"".equals(content.getContent())) {
                sql.append(" ,content='" + content.getContent() + "'");
            }
            if (content.getTopic() != null && !"".equals(content.getTopic())) {
                sql.append(" ,topic='" + content.getTopic() + "'");
            }
            if(content.getOrderid()!=null&&!"".equals(content.getOrderid())){
                sql.append(" ,orderid='"+content.getOrderid()+"'");
            }
            sql.append(" where 1=1 ");

            if (content.getId() != null && !"".equals(content.getId())) {
                sql.append(" and id='" + content.getId() + "'");
            }
            return sql.toString();
        }

        public String updateSurveyOption(Map<String, Object> map) {
            com.supermap.dubbo.api.cloud.survey.SurveyOptions surveyOptions = (com.supermap.dubbo.api.cloud.survey.SurveyOptions) map.get("surveyOptions");
            StringBuffer sql = new StringBuffer();
            sql.append("UPDATE t_surveyoptions t set");
            if (surveyOptions.getId() != null && !"".equals(surveyOptions.getId())) {
                sql.append(" t.id='" + surveyOptions.getId() + "'");
            }
            if (surveyOptions.getOption() != null && !"".equals(surveyOptions.getOption())) {
                sql.append(" ,t.option='" + surveyOptions.getOption() + "'");
            }
            if (surveyOptions.getOrderid() != null && !"".equals(surveyOptions.getOrderid())) {
                sql.append(" ,t.orderid='" + surveyOptions.getOrderid() + "'");
            }
            sql.append(" where 1=1 ");
            if (surveyOptions.getId() != null && !"".equals(surveyOptions.getId())) {
                sql.append(" and t.id='" + surveyOptions.getId() + "'");
            }

            return sql.toString();
        }

        public String getSurveyName(Map<String, Object> map) {
            String userId = (String) map.get("userId");

            StringBuffer sql = new StringBuffer();
            sql.append("select t1.id,t1.title,t1.userid from t_notice t " +
                    "RIGHT JOIN t_surveycategory t1 on t.messageid=t1.id where t.isread=1 ");
            if (userId != null && !"".equals(userId) && userId != null) {
                sql.append("and t1.userid=" + userId + "");
            }
            sql.append(" GROUP BY t1.title");
            return sql.toString();
        }

        public String getSmsUserListId(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            StringBuffer sql = new StringBuffer();
            sql.append("select t.id from t_s_base_user t " +
                    "LEFT join t_s_user_org t1 on t.id=t1.user_id and t.delete_flag=0" +
                    " LEFT JOIN t_s_depart t2 on t1.org_id=t2.id where 1=1");
            if (areaCode != null && !"".equals(areaCode) && areaCode != null) {
                sql.append(" and t2.depareacode like '" + areaCode + "%'");
            }
            return sql.toString();
        }

        public String delSurveyMenu(Map<String, Object> map) {
            String surveyId = (String) map.get("surveyid");
            String id = (String) map.get("id");
            StringBuffer sql = new StringBuffer();
            sql.append("DELETE FROM t_surveymenu where 1=1");
            if (surveyId != null && !"".equals(surveyId) && surveyId != null) {
                sql.append(" and surveyid=" + surveyId);
            }
            if (id != null && !"".equals(id) && id != null) {
                sql.append(" and id=" + id);
            }
            return sql.toString();
        }

        public String delSurveyContent(Map<String, Object> map) {

            String menuid = (String) map.get("menuid");
            String id = (String) map.get("id");
            StringBuffer sql = new StringBuffer();
            sql.append("DELETE FROM t_surveycontent where 1=1");
            if (menuid != null && !"".equals(menuid) && menuid != null) {
                sql.append(" and menuid=" + menuid);
            }
            if (id != null && !"".equals(id) && id != null) {
                sql.append(" and id=" + id);
            }
            return sql.toString();
        }

    }
}